﻿Imports System.Data.SqlClient

Public Class frmSlipMarineCargo

    Public ClassOfBuiness, LineOfBuiness, Abbreviation As String

    Sub Clear()
        Me.txtC1.Clear()
        Me.txtC2.Clear()
        Me.txtC2.Clear()
        Me.txtC3.Clear()
        Me.txtC4.Clear()
        Me.txtC5.Clear()
        Me.txtC6.Clear()
        Me.txtC7.Clear()
        Me.txtC8.Clear()
        Me.txtC9.Clear()
        Me.txtC10.Clear()
        Me.txtC11.Clear()
        Me.txtC12.Clear()
        Me.txtC13.Clear()
        For Each row As DataGridViewRow In Me.GridInsuranceCompanies.Rows
            row.Cells(Me.DGColSelect.Index).Value = False
        Next


        Me.txtC1.Focus()
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub frmSlipMarineCargo_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillInsurCompanies()
    End Sub

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.GridInsuranceCompanies.Rows.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.GridInsuranceCompanies.Rows.Add({False, InsurComp})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.txtC1.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC1, "Please fill in all details")
            Me.txtC1.Focus()

        ElseIf Me.txtC2.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC2, "Please fill in all details")
            Me.txtC2.Focus()

        ElseIf Me.txtC2.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC2, "Please fill in all details")
            Me.txtC2.Focus()

        ElseIf txtC3.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC3, "Please fill in all details")
            Me.txtC3.Focus()

        ElseIf Me.txtC4.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC4, "Please fill in all details")
            Me.txtC4.Focus()

        ElseIf Me.txtC5.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC5, "Please fill in all details")
            Me.txtC5.Focus()

        ElseIf Me.txtC6.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC6, "Please fill in all details")
            Me.txtC6.Focus()

        ElseIf Me.txtC7.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC7, "Please fill in all details")
            Me.txtC7.Focus()

        ElseIf Me.txtC8.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC8, "Please fill in all details")
            Me.txtC8.Focus()

        ElseIf Me.txtC9.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC9, "Please fill in all details")
            Me.txtC9.Focus()

        ElseIf Me.txtC10.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC10, "Please fill in all details")
            Me.txtC10.Focus()
        ElseIf Me.txtC11.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC11, "Please fill in all details")
            Me.txtC11.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor
                'Check if one or more insurance companies are selected
                Dim InsurCompanies As ArrayList = GetSelInsurCompanies()
                If InsurCompanies.Count = 0 Then
                    MsgBox("Please select the insurance company(ies) the slip is issued for")
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If
                Dim DocSNo, Year As Integer
                Dim SlipNo As String

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim X As Integer = 1


                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans
                cmd.Connection = cnn

                'Get Year
                cmd.CommandText = "Select Year(GetDate())"
                Year = CInt(cmd.ExecuteScalar)

                ''Get Doc SNo
                'cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Slips Where Year(TransDate)=Year(GetDate()) and Abbreviation=N'" & Abbreviation & "'"
                'DocSNo = CInt(cmd.ExecuteScalar) + 1
                'Get Doc SNo
                cmd.CommandText = "Select IsNull(Max(DocSNo),10000) From Slips Where Branch=@Branch and Year(TransDate)=Year(GetDate()) and Abbreviation=N'" & Abbreviation & "'"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Branch", Branch)
                DocSNo = CInt(cmd.ExecuteScalar) + 1


                For Each InsurComp As String In InsurCompanies
                    'Create SlipNo
                    SlipNo = "Slip-" & BranchCode & "-" & Abbreviation & "-" & Year & "-" & DocSNo & "-D" & X


                    ''Create SlipNo
                    'SlipNo = "Slip-" & Abbreviation & "-" & Year & "-" & DocSNo

                    'Save slip
                    cmd.CommandText = "Insert Into Slips " & _
                                      "(SlipNo,ClassOfBuiness,LineOfBuiness,Abbreviation,Type,DocSNo,Branch,InsuranceCompany,Status,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13," & _
                                      "UserName) Values " & _
                                      "(@SlipNo,@ClassOfBuiness,@LineOfBuiness,@Abbreviation,@Type,@DocSNo,@Branch,@InsuranceCompany,@Status,@C1,@C2,@C3,@C4,@C5,@C6,@C7,@C8,@C9,@C10@C11,@C12,@C13," & _
                                      "@UserName)"

                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@SlipNo", SlipNo)
                    cmd.Parameters.AddWithValue("@ClassOfBuiness", ClassOfBuiness)
                    cmd.Parameters.AddWithValue("@LineOfBuiness", LineOfBuiness)
                    cmd.Parameters.AddWithValue("@Abbreviation", Abbreviation)
                    cmd.Parameters.AddWithValue("@Type", "Insurance")
                    cmd.Parameters.AddWithValue("@DocSNo", DocSNo)
                    cmd.Parameters.AddWithValue("@Branch", Branch)
                    cmd.Parameters.AddWithValue("@InsuranceCompany", InsurComp)
                    cmd.Parameters.AddWithValue("@Status", "Sent to Insur. Comp.")
                    cmd.Parameters.AddWithValue("@C1", Me.txtC1.Text)
                    cmd.Parameters.AddWithValue("@C2", Me.txtC2.Text.Trim)
                    cmd.Parameters.AddWithValue("@C3", Me.txtC3.Text)
                    cmd.Parameters.AddWithValue("@C4", Me.txtC4.Text.Trim)
                    cmd.Parameters.AddWithValue("@C5", Me.txtC5.Text)
                    cmd.Parameters.AddWithValue("@C6", Me.txtC6.Text.Trim)
                    cmd.Parameters.AddWithValue("@C7", Me.txtC7.Text.Trim)
                    cmd.Parameters.AddWithValue("@C8", Me.txtC8.Text.Trim)
                    cmd.Parameters.AddWithValue("@C9", Me.txtC9.Text.Trim)
                    cmd.Parameters.AddWithValue("@C10", Me.txtC10.Text.Trim)
                    cmd.Parameters.AddWithValue("@C11", Me.txtC11.Text.Trim)
                    cmd.Parameters.AddWithValue("@C12", Me.txtC12.Text.Trim)
                    cmd.Parameters.AddWithValue("@C13", Me.txtC13.Text.Trim)


                    cmd.Parameters.AddWithValue("@UserName", CurrentUser)
                    cmd.ExecuteNonQuery()
                    X += 1
                Next
                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully!")

                Dim ChosenFolderPath, SlipSNo As String

                'Print
                If Me.FolderBrowserDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                    ChosenFolderPath = Me.FolderBrowserDialog1.SelectedPath
                Else
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                X = 1
                For Each InsurComp As String In InsurCompanies
                    SlipSNo = "Slip-" & BranchCode & "-" & Abbreviation & "-" & Year & "-" & DocSNo & "-D" & X

                    'PrintSlip(SlipSNo, Abbreviation)

                    Dim dap As New SqlDataAdapter("Select * From Slips Where SlipNo=@SlipNo", cnn)
                    Dim das As New DSSlips

                    dap.SelectCommand.Parameters.AddWithValue("@SlipNo", SlipSNo)
                    dap.Fill(das, "Slips")

                    Dim rpt As New SlipMAC
                    rpt.SetDataSource(das)
                    rpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, _
                                     ChosenFolderPath & "\" & InsurComp & " - " & SlipSNo & ".pdf")
                    rpt.Dispose()
                    X += 1
                Next

                System.Diagnostics.Process.Start(ChosenFolderPath)
                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If

    End Sub

    Function GetSelInsurCompanies() As ArrayList
        Try
            Dim InsurCompanies As New ArrayList

            For Each InsurComp As DataGridViewRow In Me.GridInsuranceCompanies.Rows
                If CBool(InsurComp.Cells(Me.DGColSelect.Index).Value) = True Then
                    InsurCompanies.Add(InsurComp.Cells(Me.DGColInsuranceCompany.Index).Value)
                End If
            Next

            Return InsurCompanies
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Function

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Clear()

    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreview.Click
        Me.ErrProv.Clear()
        If Me.txtC1.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC1, "Please fill in all details")
            Me.txtC1.Focus()

        ElseIf Me.txtC2.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC2, "Please fill in all details")
            Me.txtC2.Focus()

        ElseIf txtC3.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC3, "Please fill in all details")
            Me.txtC3.Focus()

        ElseIf Me.txtC4.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC4, "Please fill in all details")
            Me.txtC4.Focus()

        ElseIf Me.txtC5.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC5, "Please fill in all details")
            Me.txtC5.Focus()

        ElseIf Me.txtC6.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC6, "Please fill in all details")
            Me.txtC6.Focus()

        ElseIf Me.txtC7.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC7, "Please fill in all details")
            Me.txtC7.Focus()

        ElseIf Me.txtC8.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC8, "Please fill in all details")
            Me.txtC8.Focus()

        ElseIf Me.txtC9.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC9, "Please fill in all details")
            Me.txtC9.Focus()

        ElseIf Me.txtC10.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC10, "Please fill in all details")
            Me.txtC10.Focus()

        ElseIf Me.txtC11.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC11, "Please fill in all details")
            Me.txtC11.Focus()

        ElseIf Me.txtC12.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC12, "Please fill in all details")
            Me.txtC12.Focus()


        ElseIf Me.txtC13.Text.Length = 0 Then
            Me.ErrProv.SetError(Me.txtC13, "Please fill in all details")
            Me.txtC13.Focus()
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim das As New DSSlips

                das.Tables("Slips").Rows.Add({0, Branch, "-", 0, ClassOfBuiness, LineOfBuiness, Abbreviation, "Insurance", _
                                              "----------------", "----------------", _
                                              Me.txtC1.Text.Trim, Me.txtC2.Text.Trim, Me.txtC3.Text.Trim, Me.txtC4.Text.Trim, _
                                              Me.txtC5.Text.Trim, Me.txtC6.Text.Trim, Me.txtC7.Text.Trim, Me.txtC8.Text.Trim, _
                                              Me.txtC9.Text.Trim, Me.txtC10.Text.Trim, Me.txtC11.Text.Trim, Me.txtC12.Text.Trim, Me.txtC13.Text.Trim})

                Dim rpt As New SlipMAC
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.ShowDialog()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If

    End Sub
End Class